var express = require('express');
var router = express.Router();
// 引入mysql模块
let mysql=require("./mysql.js");
let connectine=mysql();

// 引入验证token的文件
let jwtAuth=require("./../jwtAuth/index.js");
router.use(jwtAuth);

/* 分页接口*/
router.get('/', function(req, res, next) {
    // req.query:获取到传过来的参数
    let sql=`SELECT * FROM t_student LIMIT ${req.query.startIndex},${req.query.pageNumber}`;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

// 获取学生总数接口
router.get('/allNumber', function(req, res, next) {
    // req.query:获取到传过来的参数
    let sql=`SELECT COUNT(*) FROM t_student;`;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

// 删除学生接口
router.post('/delStudent', function(req, res, next) {
    // req.body:获取到传过来的参数
    let sql=`DELETE FROM t_student WHERE classID="${req.body.classID}" AND name="${req.body.name}" AND college="${req.body.college}";`;
    // console.log(sql);
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

// 查询学生接口
router.post('/searchStudent', function(req, res, next) {
    // req.body:获取到传过来的参数
    let sql=`SELECT * FROM t_student WHERE classID="${req.body.classID}" OR name="${req.body.name}"; `;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

// 获取所有学院
router.get('/college', function(req, res, next) {
    // req.query:获取到传过来的参数
    let sql=`SELECT college FROM t_college; `;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});


// 根据学院获取专业接口
router.get('/major', function(req, res, next) {
    // req.query:获取到传过来的参数
    let sql=`SELECT major FROM t_college WHERE college="${req.query.college}"; `;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});
// 根据学院和专业获取班级接口
router.get('/grade', function(req, res, next) {
    // req.query:获取到传过来的参数
    let sql=`SELECT grade FROM t_college WHERE college="${req.query.college}" AND major="${req.query.major}"`;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});


// 新增学生接口
router.post('/addStudent', function(req, res, next) {
    // req.body:获取到传过来的参数
    let sql=`INSERT INTO t_student (classID,name,college,major,grade,classNumber,age) VALUES ("${req.body.classID}","${req.body.name}","${req.body.college}","${req.body.major}",${req.body.grade},${req.body.classNumber},${req.body.age});`;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

// 修改学生接口
router.post('/changeStudent', function(req, res, next) {
    // req.body:获取到传过来的参数
    let sql=`UPDATE t_student set name="${req.body.name}",college="${req.body.college}",major="${req.body.major}",classNumber="${req.body.classNumber}",age="${req.body.age}" WHERE classID="${req.body.classID}";`;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

// 获取学院学生数据接口
router.get('/collegeStudent', function(req, res, next) {
    // req.body:获取到传过来的参数
    let sql=`SELECT college FROM t_student; `;
    connectine.query(sql,function(err,result){
        if(err){
            console.log(err);
            res.send({
                code:400,
                data:{}
            })
        }else{
            res.send({
                code:200,
                data:result
            })
        }
    })


});

module.exports = router;